package fact.view.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.sql.DataSource;

import fact.view.DTO.UserDTO;

@ManagedBean(name = "userModelBean")
@RequestScoped
public class UserModelBean {
	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	public ArrayList<UserDTO> getUserList() throws SQLException
	{
		ArrayList<UserDTO> userList=new ArrayList<UserDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select userid,password,username,ROLE_ID,ROLE_NAME, " +
				"case  " +
				"when ISACTIVE = 1 then 'Active' " +
				"when ISACTIVE = 0 then 'Inactive' " +
				"else 'NA' " +
				"end ISACTIVE " +
				"  from MST_USER,MST_ROLE " +
				"Where MST_ROLE.ROLE_ID=MST_USER.USER_TYPE ORDER BY USERNAME ");
		
		ResultSet result =  ps.executeQuery();
		UserDTO u=null;
				while(result.next())
				{
					u=new UserDTO();
					u.setUserId(result.getString("userid"));
					u.setPassword(result.getString("password"));
					u.setUserName(result.getString("username"));
					u.setUserRoleId(result.getInt("ROLE_ID"));
					u.setUserRoleName(result.getString("ROLE_NAME"));
					u.setIsActive(result.getString("ISACTIVE"));
					
					userList.add(u);
				}
		
		
		return userList;
	}
	
	public UserDTO getUser(String userId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select userid,password,username,ROLE_ID,ROLE_NAME, " +
				"case  " +
				"when ISACTIVE = 1 then 'Active' " +
				"when ISACTIVE = 0 then 'Inactive' " +
				"else 'NA' " +
				"end ISACTIVE " +
				"  from MST_USER,MST_ROLE " +
				"Where MST_ROLE.ROLE_ID=MST_USER.USER_TYPE and upper(USERID)=upper(?) ORDER BY USERNAME ");
		
		ps.setString(1, userId);
		ResultSet result =  ps.executeQuery();
		UserDTO u=null;
				if(result.next())
				{
					u=new UserDTO();
					u.setUserId(result.getString("userid"));
					u.setPassword(result.getString("password"));
					u.setUserName(result.getString("username"));
					u.setUserRoleId(result.getInt("ROLE_ID"));
					u.setUserRoleName(result.getString("ROLE_NAME"));
					u.setIsActive(result.getString("ISACTIVE"));
				}
		
		return u;
	}
	
	public boolean addUser(UserDTO user,String addedBy) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Insert into Mst_User(Userid,password,username,isactive,user_type,CREATED_BY) " +
				"Values(?,?,?,?,?,?)");  
		ps.setString(1, user.getUserId());
		ps.setString(2, user.getPassword());
		ps.setString(3, user.getUserName());
		ps.setString(4, user.getIsActive());		
		ps.setInt(5, user.getUserRoleId());
		ps.setString(6, addedBy);
		
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean deleteUser(String userId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Delete MST_USER Where USERID=?");  
		ps.setString(1,userId);
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	

	public boolean editUser(UserDTO user) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Update MST_USER Set USERNAME=?,PASSWORD=?,ISACTIVE=?,USER_TYPE=? Where USERID=?");  
		ps.setString(1, user.getUserName());
		ps.setString(2, user.getPassword());
		ps.setString(3, user.getIsActive());
		ps.setInt(4, user.getUserRoleId());
		ps.setString(5, user.getUserId());
		
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
		

}